#encoding=utf-8
import psycopg2
import time
import configparser
import urllib
import urllib2
import thread
import sys
reload(sys)
sys.setdefaultencoding('utf-8')

parking_name = ""
#------db config---------
db_host = ""
db_user = ""
db_passwd = ""
db_database = ""
db_charset = ""
db_port = ""
#------------------------
conn = None
#-----db table config----
tableName = ""  # split by '#',example: tableA#tableB#tableC
SQL = ""
#-----server config------
server_data_receiver = ""
server_heart_receiver = ""
server_error_receiver = ""
#------the time to sleep of query database---------
queryTime = 5

#-------------------------------------
connection_is_active = True

def readConfig(): 
	global parking_name
	#------------------------
	global db_host
	global db_user
	global db_passwd
	global db_database
	global db_charset
	global db_port
	#-----server config------
	global server_data_receiver
	global server_heart_receiver
	global server_error_receiver
	#------------------------------------------------------------
	global queryTime
	try:
		cf = configparser.ConfigParser()
		cf.read("param.conf")
		
		parking_name = cf.get("parking","parking_name").strip()
		#----------------------------------------------
		db_host = cf.get("db", "db_host").strip()
		db_user = cf.get("db", "db_user").strip()
		db_passwd = cf.get("db", "db_passwd").strip()
		db_database = cf.get("db", "db_database").strip()
		db_charset = cf.get("db", "db_charset").strip()
		db_port = cf.get("db", "db_port").strip()
		#-------------------------------------------------------------
		#-------------------------------------------------------------
		server_data_receiver = cf.get("server", "server_data_receiver").strip()
		server_heart_receiver = cf.get("server", "server_heart_receiver").strip()
		server_error_receiver = cf.get("server", "server_error_receiver").strip()

		queryTime = int(cf.get("time", "queryTime").strip())
	except BaseException,e:
		sendError(501,repr(e),True)
	print("*-----read param config success-----------")
	print("*    db_host     :"+db_host)
	print("*    db_user     :"+db_user)
	print("*    db_passwd   :"+db_passwd)
	print("*    db_database :"+db_database)
	print("*    db_port     :"+db_port)
	print("*    db_charset  :"+db_charset)
	print("*    query time  :"+str(queryTime)+"s span")
	print("*    Author      :"+"ypb")
	print("*    Author email:"+"ypbck@qq.com")
	print("*-----------------------------------------")

# create database connection
def createConnection():
	global connection_is_active
	global conn
	times = 0
	while times<120:
		try:
			conn = psycopg2.connect(database=db_database, user=db_user, password=db_passwd, host=db_host, port=db_port)
			if(conn):
				print("get connection successed")
				connection_is_active = True
				print("*-----------------------------------------")
				return conn
		except BaseException,e:
			print("get connection failed")
			if(times==119):
				sendError(300,repr(e),True)
			else:
				sendError(300,repr(e),False)
		times = times+1
		time.sleep(180)

# close connection
def closeConnection():
	conn.close()

# query data
def query(table,sql):
	global connection_is_active
	try:
		#print("start query")
		cursor = conn.cursor()
		cursor.execute(sql)
		resList = cursor.fetchall()
		if(resList):
			delete(table)
		cursor.close()
		return resList
	except BaseException,e:
		connection_is_active = False
		cursor.close()
		thread.start_new_thread(sendError,(301,repr(e),False))
		return []

def delete(table):
	#print("start delete record")
	cursor = conn.cursor()
	try:
		cursor.execute("delete from "+table)
		conn.commit()
		cursor.close()
	except BaseException,e:
		conn.rollback()
		cursor.close()
		thread.start_new_thread(sendError,(302,repr(e),False))

	#print("delete record successed")
def dealRes(p,table):
	try:
		if(p==[]):
			return
		res = ""
		for i in p:
			for x in i:
				#print(str(x).strip()+"#")
				res = res+str(x).strip()+"#"
			res = res+"##"
		res = res.decode("string_escape")
		res = res.replace("None","null")
		print(u"catch the update data in table '"+table+"' :\n"+res)
		thread.start_new_thread(sendData,(table, res))
	except BaseException,e:
		thread.start_new_thread(sendError,(502,repr(e),False))

def sendData(table,data):
	print("start send data to server of table "+table)
	try:
		test_data = {"parkingName":parking_name,'data':data}
		test_data_urlencode = urllib.urlencode(test_data)
		req = urllib2.Request(url = server_data_receiver,data =test_data_urlencode)
		f = urllib2.urlopen(req)
		f.close()
	except BaseException:
		print("network not avisable")

def sendHeart():
	global tableName
	global SQL
	print("send heart to server...")
	try:
		test_data = {"parkingName":parking_name,'heart':"HM"}
		test_data_urlencode = urllib.urlencode(test_data)
		req = urllib2.Request(url = server_heart_receiver,data =test_data_urlencode)
		f = urllib2.urlopen(req)
		res = f.read()
		res = res.replace("\n","")
		if(len(res)>5):
			tableName = res.split("##")[0].split("#")
			SQL = res.split("##")[1].split("#")
		f.close()
	except BaseException,e:
		print("network not avisable:"+repr(e))

def sendError(errorCode,msg,isExit):
	print("send error to server...")
	try:
		exit = 0
		if(isExit):
			exit = 1
		test_data = {"parkingName":parking_name,'errorCode':errorCode,"errorMsg":msg,"isExit":exit}
		test_data_urlencode = urllib.urlencode(test_data)
		req = urllib2.Request(url = server_error_receiver,data =test_data_urlencode)
		f = urllib2.urlopen(req)
		f.close()
	except BaseException:
		print("network not avisable")
	if(isExit):
		sys.exit(-1)

def selectData():
	try:
		index = 0
		for sql in SQL:
			reslist = query("ypb_"+tableName[index],sql)
			dealRes(reslist,tableName[index])
			index = index+1
	except BaseException,e:
		thread.start_new_thread(sendError,(503,repr(e),False))

def createBackupTable(tablename):
	cursor = conn.cursor()
	try:
		cursor.execute("select * from ypb_"+tablename)
	except BaseException: #no table named xxx,so create it!
		try:
			print("####no table named 'ypb_"+tablename+"',will create it")
			sql = "select * into ypb_"+tablename+" from "+tablename+" limit 1"
			#print(sql)
			conn.commit()
			cursor.execute(sql)
			conn.commit()
			print("--->create table success!")
			createBackupTrigger(tablename)
		except BaseException,e:
			print("****create table failed")
			thread.start_new_thread(sendError,(303,repr(e),False))
	cursor.close()
		
def createBackupTrigger(tablename):
	triggerName = "ypb_"+tablename+"_tigger()"
	funsql = "CREATE OR REPLACE FUNCTION "+triggerName+" RETURNS TRIGGER AS $$ BEGIN "
	funsql += " INSERT INTO ypb_"+tablename+" VALUES(NEW.*);"
	funsql += "RETURN null;  END; $$ LANGUAGE plpgsql;"

	triggersql = "CREATE TRIGGER ypb_"+tablename+" AFTER INSERT OR UPDATE ON "
	triggersql += tablename+" FOR EACH ROW EXECUTE PROCEDURE "+triggerName
	cursor = conn.cursor()
	try:
		cursor.execute(funsql)
		conn.commit()
		cursor.execute(triggersql)
		conn.commit()
		print("--->create  trigger and trigger function success!")
	except BaseException,e: #no table named xxx,so create it!
		conn.rollback()
		print("****create  trigger and trigger function success! filed!")
		print("****operate alredy rollback,nothing has been done")
		thread.start_new_thread(sendError,(503,repr(e),False))
	cursor.close()

def initPostgres():
	if(len(tableName)==0):
		return
	for t in tableName:
		createBackupTable(t)
	print("--->init postgresql successed!")
	print("*-----------------------------------------\n")

def main():
	time.sleep(30)
	readConfig()
	createConnection()
	sendHeart()
	initPostgres()
	times = 0
	while True:
		selectData()
		time.sleep(queryTime)
		times = times+1
		if(times%20==0): #10minute
			sendHeart()
			initPostgres()
		if(not connection_is_active):
			print("renew database connection!")
			closeConnection()
			createConnection()

if __name__ == '__main__':
	main()
